import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class Query {
	private Statement stmt=null;
	private ResultSet rs=null;
	private Connection conn=null;

	public Statement getStmt() {
		return stmt;
	}
	public void setStmt(Statement stmt) {
		this.stmt = stmt;
	}
	public ResultSet getRs() {
		return rs;
	}
	public void setRs(ResultSet rs) {
		this.rs = rs;
	}
	public Connection getConn() {
		return conn;
	}
	public void setConn(Connection conn) {
		this.conn = conn;
	}
		
	public UserBean QueryAllFromUserinfo(String username) {
		Connect con=new Connect();
		Connection conn=con.initial();
		
		try {
			stmt=conn.createStatement();
			String sql="select * from userinfo where username='"+username+"'";
			rs=stmt.executeQuery(sql);
			UserBean UB=new UserBean();
			if(rs.next()){
				UB.setUsername(rs.getString("username"));
				UB.setPassword(rs.getString("password"));
				UB.setAdmin(rs.getBoolean("admin"));
				UB.setID(rs.getInt("id"));

				return UB;
				}
			else{
				return UB;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
		finally
		{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public NBAteamBean QueryAllFromNBAteam(int id){
		Connect con=new Connect();
		Connection conn=con.initial();
		
		try {
			stmt=conn.createStatement();
		
			String sql="select * from NBAteam where id="+String.valueOf(id);
			rs=stmt.executeQuery(sql);
			NBAteamBean NBAB=new NBAteamBean();
			if(rs.next()){
				NBAB.setTeamname(rs.getString("teamname"));
				NBAB.setTeamstar(rs.getString("teamstar"));
				NBAB.setCoach(rs.getString("coach"));
				NBAB.setArea(rs.getString("area"));
			
				return NBAB;
				}
			else{
				return NBAB;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			}
			finally
			{
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
	}
	
	public TeaminfoBean QueryAllFromteaminfo(int id) throws SQLException{
		Connect con=new Connect();
		Connection conn=con.initial();
		
		try {
			stmt=conn.createStatement();
		
			String sql="select * from teaminfo where id="+String.valueOf(id);
			rs=stmt.executeQuery(sql);
			TeaminfoBean TB=new TeaminfoBean();
			if(rs.next()){
				TB.setTeamname(rs.getString("teamname"));
				TB.setTeaminfo(rs.getString("teaminfo"));

				return TB;
				}
			else{
				return TB;
				}
			}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			}
			finally
			{
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}		
	}
	
	public StarinfoBean QueryAllFromstarinfo(int id) throws SQLException{
		Connect con=new Connect();
		Connection conn=con.initial();
		
		try {
			stmt=conn.createStatement();
		
			String sql="select * from starinfo where id="+String.valueOf(id);
			rs=stmt.executeQuery(sql);
			StarinfoBean SB=new StarinfoBean();
			if(rs.next()){
				SB.setStarname(rs.getString("starname"));
				SB.setStarinfo(rs.getString("starinfo"));
				
				return SB;
			}else{
				return SB;
				}
			}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			}
			finally
			{
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}	
	}
	
	public ArrayList QueryByArea(String area) throws SQLException{
		Connect con=new Connect();
		Connection conn=con.initial();
		
		try {
			stmt=conn.createStatement();
		
			String sql="select * from NBAteam where area='"+area+"'";
			rs=stmt.executeQuery(sql);
			ArrayList<NBAteamBean> NBAteamlistbyarea=new ArrayList <NBAteamBean>();
			while(rs.next())
			{
				NBAteamBean NBAteambean=new NBAteamBean();
				NBAteambean.setTeamname(rs.getString("teamname"));
				NBAteambean.setTeamstar(rs.getString("teamstar"));
				NBAteambean.setCoach(rs.getString("coach"));
				NBAteambean.setArea(rs.getString("area"));
				NBAteamlistbyarea.add(NBAteambean);
			}
			return NBAteamlistbyarea;
			}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			}
			finally
			{
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}	
	} 
	
	
}
